﻿using System;
using System.Data;
using MySql.Data.MySqlClient;  
using RouteMaster;

/// <summary>
/// Summary description for MySQLHelper
/// </summary>
public class MySQLHelper
{
    static readonly string _connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
         "anotherurl.com", "root", "rootpw!", "laostraveller");

    public const string BUS_QUERY = "bus_query";
    const string ROUTE_HOPS = "route_hops";
    const string ROUTE_NAMES = "route_names";

    public static string ConnectionString { get { return _connStr; } }

    public readonly DataSet _ds = new DataSet();

    public RealWorld _world = new RealWorld();

    public void ReadRoutesDataSet()
    {
        if (!_ds.Tables.Contains(BUS_QUERY))
        {
            try
            {
                using (var conn = new MySqlConnection(ConnectionString))
                {
                    conn.Open();

                    string sql = "select bs_id, bus_stations.name as Stop, places.name as Place, description, latitude, longitude, zoom_level from bus_stations " +
                                 "left join places " +
                                 "on bus_stations.place_id = places.place_id " +
                                 "order by Place, Stop ;";

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        using (var myReader = cmd.ExecuteReader())
                        {
                            _ds.Load(myReader, LoadOption.OverwriteChanges, new[] { BUS_QUERY });
                            myReader.Close();
                        }
                    }

                    sql = "select route_id, stop, bs_id, duration from route_hops order by route_id, stop;";

                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        var myReader = cmd.ExecuteReader();
                        _ds.Load(myReader, LoadOption.PreserveChanges, new[] { ROUTE_HOPS });
                        myReader.Close();
                    }

                    sql = "select route_id, name, vehicleType from routename;";
                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        MySqlDataReader myReader = cmd.ExecuteReader();
                        _ds.Load(myReader, LoadOption.PreserveChanges, new[] { ROUTE_NAMES });
                        myReader.Close();
                    }

                    conn.Close();
                }

                PopulateMyWorld();
            }
            catch (Exception)
            {
            }
        }
    }


    private void PopulateMyWorld()
    {
        _world = new RealWorld();

        // create all the bustops in Laos

        if (!_ds.Tables.Contains(BUS_QUERY))
            return;

        foreach (DataRow row in _ds.Tables[BUS_QUERY].Rows)
        {
            string id = "" + row[0];
            string busStopName = "" + row[1] + ", " + row[2];
            _world.AddBusStop(int.Parse(id), busStopName);
        }

        if (!_ds.Tables.Contains(ROUTE_NAMES))
            return;

        foreach (DataRow row in _ds.Tables[ROUTE_NAMES].Rows)
        {
            int routeID = int.Parse("" + row[0]);
            string routeName = "" + row[1];
            string vehicleType = "" + row[2];
            _world.TryGetRoute(routeID, routeName, vehicleType);
        }

        if (!_ds.Tables.Contains(ROUTE_HOPS))
            return;

        foreach (DataRow row in _ds.Tables[ROUTE_HOPS].Rows)
        {
            //route_id, stop, bs_id, duration

            int routeID = int.Parse("" + row[0]);
           // int stopSequence = int.Parse("" + row[1]);
            int bsID = int.Parse("" + row[2]);
            int duration = int.Parse("" + row[3]);

            try
            {
                BusRoute busRoute = _world.GetRoute(routeID);
                busRoute.AddStop(bsID, duration == 0 ? null : new Edge(new TimeSpan(0, duration, 0)));
            }
            catch (Exception)
            {
            }
        }
    }
}


